Web Applications and XML

Up to this point, we have spent a great deal of time on discussing client-side technology and XML.  Now we are going to address the issue of where the XML that we send to the client exists.

More often than not, XML is not going to be the format that you permanently store your business data.  While you can, XML is not the perfect mechanism for that.  Usually, a relational database (or database of some sort) is more appropriate for this since a database provides the ability to ensure data validity.  This is not to be confused with XML validity.  Data validity has to do with business rules, something that XML does not handle at all.  Instead, XML is a perfect data representation and communication vehicle.  Quite often you will find that dynamically generating XML from a database is the ideal way to communicate between client (browser) and server (web server).

Another aspect that we want to point out is the client is not the only place that you can do application processing.  The server is really the central location for a web application.  Everything is really located on the server and distributed to the client.  There are a lot of things that we can do on the server before the client even has a chance to process anything that we return to them.  In other words, we can dynamically generate the content that we are going to send to the client.

Before we get into that, let’s look at the Database and how it relates to XML.

The Database

First, let’s look at our example database.  In it we have modeled the Employees example that up to this point has been entirely XML based.  To start, the example database that we are going to use is a Microsoft Access 2000 database.  It contains three tables and has been pre-populated with the data that was in our XML document.

The following diagram shows the table structures and the relationships between them.

File: Chapter 8\Employees.mdb in Microsoft Access (Relationship View)

The following is a breakdown of the tables and the relationships between them.

Employees Table

The “employees” table has 8 distinct columns (or elements).

	id – unique ID for the employee.  Since this column is our 
	primary key, we are guaranteed that it will be unique.
	firstname – the first name of the employee.
	lastname – the last name of the employee.
	position – the role of the employee in the organization.
	street – address street.
	city – address city.
	state – address state.
	zip – address zip.

Phone Types Table

The “phonetypes” table only has one column (element).

	phonetype – this is the type of phone number (e.g. main, fax, 
	home, etc.).  This column is a primary key so it is guaranteed 
	to be unique.

Phone Numbers Table

The “phonenumbers” table has three columns (elements).

	id – this column maps to the “id” column in the “employees” table 
	in a foreign key relationship (there can be multiple phone numbers 
	for a single employee).  It is also part of a compound primary key 
	with the “phonetype” column, and we will explain that next.

	phonetype – this column maps to the phonetype column in the 
	phonetypes table in a foreign key relationship (there can be 
	multiple types of phone numbers for a single employee).

This column in association with the “id” column make the primary key for this table.  This compound primary key make it impossible to have two phone numbers of the same type.  While this may not be advantages in the real world, it suites our example.

	phonenumber – this column contains an instance of a phone 
	number for an employee.

Let’s add a few more rules to our content model that will make our data valuable.  Let’s make some columns required (data must exist in them), while others are optional.

Remember, fields that are defined as primary and foreign keys are already required columns as a rule of relational database theory.

Employees Table

	Required: id, firstname, lastname, position
	Optional: street, city, state, zip

Phone Types Table

	Required: phonetype

Phone Numbers Table

	Required: id, phonetype, phonenumber

We have now created a relational database (entity) that models the content that we want to store in it.  We have specified the columns (elements) that are contained within the database, and we have defined the rules for required and optional content.

If you are working against a server database such as Microsoft SQL Server, ORACLE, or DB2, instead of using an interactive environment like Microsoft Access to create a database, you might create a SQL (Structured Query Language) script.  The following script works with Microsoft SQL Server 7.0 and higher.

CREATE TABLE employees
(
	id			VARCHAR(50)	NOT NULL,
	firstname	VARCHAR(50)	NOT NULL,
	lastname		VARCHAR(50)	NOT NULL,
	street		VARCHAR(50)	NULL,
	city		VARCHAR(50)	NULL,
	state		VARCHAR(50)	NULL,
	zip		VARCHAR(50)	NULL,

	CONSTRAINT PRIMARY KEY CLUSTERED ( id )
)
GO

CREATE TABLE phonetypes
(
	phonetype	VARCHAR(50)	NOT NULL,

	CONSTRAINT PRIMARY KEY CLUSTERED ( phonetype )
)
GO

CREATE TABLE phonenumbers
(
	id			VARCHAR(50)	NOT NULL,
	phonetype	VARCHAR(50)	NOT NULL,
	phonenumber	VARCHAR(50)	NOT NULL,

	CONSTRAINT PRIMARY KEY CLUSTERED ( id, phonetype ),

	CONSTRAINT FOREIGN KEY ( id )
		REFERENCES employees ( id ),

	CONSTRAINT FOREIGN KEY ( phonetype )
		REFERENCES phonetypes ( phonetype )
)
GO

While the syntax and development environments may change between database products, the end results are the same.  A database storage object (entity) is created that has defined rules that comprise the content model.

The advantages of using a relational database are many.  A few of the advantages are:

	The structure inherently protects us from invalid data.
	We can develop software to access the database provided we know 
	the structure of the content model.
	We can query the database and view the content model.

The biggest downside is that our database is not geared for use on the Internet or via Internet protocols (HTTP, FTP, etc.).  Second, in the case of our Microsoft Access database, it is specific to the Microsoft Windows platform.  

This is where XML comes in to play.  XML is geared towards working with Internet technologies and is platform independent.  What’s even better is that we do not lose the advantages that we got when we developed our content model using a relational database.  In XML, we can control the content model, just like we did in Access and SQL Server.

Now, let’s look at using the database and server side scripting with Active Server Pages (APS’s) to dynamically generate the XML content used on the client.

Web Applications and the Web Server

Now that we have our database designed, how does the client (browser) interact or even know of the database.  The answer is simple: it doesn’t.  There is something in between the client and the database that accesses the database and dynamically generates the HTML and XML and returns it to the client for processing.

In actuality, the browser can only handle HTML and HTML related technologies.  It doesn’t care how the HTML gets to it outside of the fact that it sends a URL through an HTTP channel and receives HTML in return.  That is all the browser can handle.  What happens between the time that the URL is received by the server and HTML is returned to the client is where we can have some fun.

Dynamic generation of HTML is really nothing new.  It has been around for as long as the Web itself.  The concept is simple.  When the browser requests a URL, it doesn’t have to request an HTML document only.  It can put anything it wants on the URL, the server just has to understand what to do with it.  For example, you can run programs on the server that generate HTML that is returned to the client.  These server-side programs are often referred to as CGI’s or Common Gateway Interface programs.  

The most common forms of CGI’s are Perl scripts that contain the extension .cgi.  Perl is a scripting language common in Unix.  A call to a Perl script from a URL might look like this:
http://www.davidharding.com/getquotes.cgi

When this URL is received by the server, it takes sees that it is calling a .cgi scripting file and executes it on the server.  It redirects the applications standard output (stdout) to its own memory buffer and returns it to the client for processing.  The Web server assumes that the content that you are returning can be understood by the calling application.  In the case of a browser, it returns HTML. 

To give an example of how a CGI generates HTML and sends it to stdout, let’s look at a sample snippet of a CGI written in the C language.  Notice its use of the printf() function to print the HTML to stdout.

. . . 
printf( “<HTML>\n” );
printf( “<HEAD>\n” );
printf( “\t<TITLE>This is my home page</TITLE>\n” );
printf( “</HEAD>\n” );
. . . 

The equivalent C++ code would look something like this:

cout << “<HTML>\n”;
cout << “<HEAD>\n”;
cout << “\t<TITLE>This is my home page</TITLE>\n”;
cout << “</HEAD>\n”;

If this code were run from a command line, the output would go directly to the screen.  When run by the Web server, however, the text is captured before it goes to the screen and is stored in a memory buffer.  When the application finishes executing, the captured output is send to the client browser.  The browser treats this output the same way it treats any other HTML page and displays it.

The CGI can do any number of things while it is generating the necessary output (i.e. HTML and/or XML).  For instance it can access other applications.  It can access databases or other XML documents.  It can then use the data that it retrieves to create new output, which it in turn sends to the client.  This is how dynamic Web sites work.

Common server-side applications are written in languages such as:

	Perl
	C/C++
	VBScript (Active Server Pages)
	JavaScript (Active Server Pages / JavaScript Server Pages)

Active Server Pages are becoming very popular due to their ease of development.  For example, you can use VBScript or JavaScript to create server-side scripting that returns data to the client from a database.  Script code is easier to write and incorporates both HTML and the scripting code, so it is easier to maintain.  An example snippet of ASP code might look like the following:

<BODY>
<H1>Welcome Back, <%= database1( “CustomerName” ) %>
</H1>
<H2>Main Menu</H2>

Notice the use of two new tag types: <% and %>.  These denote server side script blocks.  You will see more of these in use later.  A typical call to an Active Server Page might look like this:
http://www.davidharding.com/getquotes.asp

Let’s look at some examples where we generate XML from a database using Active Server Pages and server-side scripting.

Generating XML Data Islands

For our first example, we are going to generate an XML Data Island using the contents of the database.  First, let’s look at the completed result of our example:

File: Chapter 9\EmployeeDataIsland2.asp

Notice that the example looks exactly the same as the pure HTML version of the same page.  In fact, the HTML that the client processes is exactly the same.  However, what goes on when the server returns the data is entirely different.  In the HTML version of this example, the HTML document is static.  In other words, the content of the HTML document resides physically within the HTML document that resides on a disk on the server.  If the XML data ever changes, it has to be changed within the XML Data Island that is physically embedded in the HTML document.  In our current (ASP) example, the XML Data Island is generated dynamically using the data in our Microsoft Access database.  If the data ever changes in the database, the changes will be reflected each time that the page is retrieved.

Let’s look at the source code that generates the XML Data Island based on the contents of our database.

</HEAD>
<%
	Dim oConn		' ADO connection object.
	Dim oRs			' Employee recordset object.
	Dim oRsPhone		' Emp phone recordset object.
	Dim strSql		' SQL string.

	' Establish a connection to the database.
	Set oConn = Server.CreateObject("ADODB.Connection")
	Call oConn.Open("DSN=EmployeesMDB;UID=Admin;PWD=;")
		
	' Open a recordset containing all of the employees.
	Set oRs = Server.CreateObject( "ADODB.Recordset" )
	strSql = "SELECT * FROM employees " &
			"ORDER BY lastname, firstname"
	Call oRs.Open( strSql, oConn )

	Response.Write( "<XML ID=employees>" & vbCrLf )
	Response.Write( "<employees>" & vbCrLf)

	' Iterate through all of the employees in
	' the database and generate the appropriate
	' XML document that contains their information.	
	While Not oRs.EOF

		' Display all of the employee information
		' that is in the employee table.
		Response.Write( "<employee>" & vbCrLf )
		Response.Write( "<name>" & vbCrLf )
		Response.Write( "<first>" & oRs("firstname") & 
				    "</first>" & vbCrLf )
		Response.Write( "<last>" & oRs("lastname") & 
				    "</last>" & vbCrLf )
		Response.Write( "</name>" & vbCrLf )
		Response.Write( "<position>" & oRs("position") & 
				    "</position>" & vbCrLf )
		Response.Write( "    <phone>" & vbCrLf )
		
		' Get the phone numbers for this employee
		' and generate the appropriate tags and content.
		Set oRsPhones = 
			Server.CreateObject("ADODB.Recordset")
	strSql = "SELECT * FROM phonenumber”
	Call oRsPhones.Open( strSql, oConn )
		
		While Not oRsPhones.EOF
			Response.Write( "<" & 
				oRsPhones("phonetype") & ">" )
			Response.Write( oRsPhones("phonenumber") )
			Response.Write( "</" & 
			oRsPhones("phonetype") & ">" & vbCrLf )
			oRsPhones.MoveNext 
		Wend

		Response.Write( "</phone>" & vbCrLf )
		Response.Write( "</employee>" & vbCrLf & vbCrLf )
		oRs.MoveNext 
	Wend
	
	oRsPhones.Close
	oRs.Close
	oConn.Close 
	
	Response.Write( "</employees>" & vbCrLf )
	Response.Write( "</XML>" & vbCrLf & vbCrLf )
%>
<BODY>

File: Chapter 9\EmployeeDataIsland2.asp

Let’s look at this code in detail.  First, notice the use of the <% and %> tags.  These denote the begin and end scripting blocks.  Alternately, we could use the <SCRIPT> tag provided we use the RUNAT attribute to specify that the script code should be run on the server.  Without the RUNAT attribute, the script will be sent to the client for processing. Here is an example:

<SCRIPT LANGUAGE=”VBSCRIPT” RUNAT=”SERVER”>
. . . (server-side script code here…) . . .
</SCRIPT>

Either way, the Web server (in this case Microsoft Internet Information Server) will load the scripting engine on the server and execute the code.

Warning:	Active Server Pages (ASP’s) must be run from a Web server 
	like Microsoft Internet Information Server (IIS).  They 
	cannot be opened directly (i.e. from the disk), because there 
	is no scripting engine support for the server-side script within 
	the browser.

Sending HTML to the Browser

The next point of note is all of the objects that we are using.  The most obvious of these is the Response object.  This object is one of the intrinsic objects to the IIS scripting engine, often called the Scripting Object Model, or SOM.  The Response object is used to send output to the client.  In the CGI example where we were writing HTML to stdout.  In ASP’s, we accomplish the same task by passing HTML into the Response object’s Write() method.

Response.Write( </phone>” & vbCrLf )

In the above example, we write the terminating </phone> tag and a new line character to the output buffer that is returned to the client as the page requested.  

In this example, all that the browser sees is:

</phone>

As you can  see, it is easy to dynamically generate HTML and send it to the browser for processing (i.e. client viewing).

Accessing the Database

The next set of objects that we will discuss are those that we use to access the database.  In our example, we are using the Microsoft Active Data Objects, commonly referred to as ADO.  

Dim oConn		' ADO connection object.
Dim oRs		' Employee recordset object.
Dim oRsPhone	' Emp phone recordset object.
Dim strSql		' SQL string.

' Establish a connection to the database.
Set oConn = Server.CreateObject("ADODB.Connection")
Call oConn.Open("DSN=EmployeesMDB;UID=Admin;PWD=;")
		
' Open a recordset containing all of the employees.
Set oRs = Server.CreateObject( "ADODB.Recordset" )
strSql = "SELECT * FROM employees " &
		   "ORDER BY lastname, firstname"
Call oRs.Open( strSql, oConn )

The first thing we do is declare four variables.  Server-side scripting in VBScript provides support for only one data type: Variant.  This is not a limitation because a Variant by definition can hold anything.  We are going to use them to point to objects that we create.

The first object that we create is an ADO Connection object.  Through this object, we are going to connect to the database.  We have to instantiate an ADO connection object on the server.  This is done using the Server object’s CreateObject() method.  The Server object is another SOM object, which provides access to server side services.  The CreateObject() method instantiates an ADO connection object and returns it to us, which we assign to the “oConn” variable.

Once the connection object has been instantiated, we can open the database using its Open() method.  The Open() method uses a standard ODBC Data Source Name (DSN), which is defined using the ODBC Administrator in the control panel.

ODBC Administrator Main Screen

Warning:	When creating ODBC Data Source Names for use in your 
	web applications with IIS, make sure that you create System 
	DSN’s, and not Local DSN’s.  ASP’s can’t work with Local 
	DSN’s since they are associated with a specific user only.

Now that we have connected to the database, let’s query it for the information that we want to populate in our XML Data Island.  This is done using an ADO Recordset object.  Just like the ADO Connection object, we must create it using the Server.CreateObject() method.

' Open a recordset containing all of the employees.
Set oRs = Server.CreateObject( "ADODB.Recordset" )
strSql = "SELECT * FROM employees " &
		   "ORDER BY lastname, firstname"
Call oRs.Open( strSql, oConn )

Once the recordset object has been created, we create a SQL statement, which defines what data we want to return from the database.  We then open the recordset using by passing the SQL statement and the open connection object to the recordset’s Open() method.

Now that we have an open recordset from the database, we can iterate through the rows and populate the XML Data Island.

' Iterate through all of the employees in
' the database and generate the appropriate
' XML document that contains their information.	
While Not oRs.EOF

	' Display all of the employee information
	' that is in the employee table.
	Response.Write( "<employee>" & vbCrLf )
	Response.Write( "<name>" & vbCrLf )
	Response.Write( "<first>" & oRs("firstname") & 
			    "</first>" & vbCrLf )
	Response.Write( "<last>" & oRs("lastname") & 
			    "</last>" & vbCrLf )
	Response.Write( "</name>" & vbCrLf )
	Response.Write( "<position>" & oRs("position") & 
			    "</position>" & vbCrLf )
	Response.Write( "    <phone>" & vbCrLf )
		
	' Get the phone numbers for this employee
	' and generate the appropriate tags and content.
	Set oRsPhones = 
		Server.CreateObject("ADODB.Recordset")
strSql = "SELECT * FROM phonenumber”
Call oRsPhones.Open( strSql, oConn )
		
	While Not oRsPhones.EOF
		Response.Write( "<" & 
			oRsPhones("phonetype") & ">" )
		Response.Write( oRsPhones("phonenumber") )
		Response.Write( "</" & 
		oRsPhones("phonetype") & ">" & vbCrLf )
		oRsPhones.MoveNext 
	Wend

	Response.Write( "</phone>" & vbCrLf )
	Response.Write( "</employee>" & vbCrLf & vbCrLf )
	oRs.MoveNext 
Wend

Notice the use of the For…Next loop to iterate through the rows in the recordset object.

While Not oRs.EOF
. . .
Wend

Through the recordset object, we can reference each individual column by name as is shown in the following example:

Response.Write( oRsPhones("phonenumber") )

Here we are writing the contents of the “phonenumber” column of the current row in the recordset to the HTTP output stream, which in turn is received by the client and processed.

The result is a complete XML Data Island that looks exactly the one in our static example.  Here is the generated XML Data Island:

<XML ID=employees>
<employees>
    <employee>
        <name>
            <first>John</first>
            <last>Doe</last>
        </name>
        <position>Programmer</position>
        <phone>
            <main>(714) 555-1000</main>
        </phone>
    </employee>
    <employee>
        <name>
            <first>Jane</first>
            <last>Doe</last>
        </name>
        <position>Systems Analyst</position>
        <phone>
            <main>(714) 555-2000</main>
        </phone>
    </employee>
</employees>
</XML>

If the data in the database changes, we will receive that data in our XML Data Island the next time that we query the page.